const express = require('express');
const mysql = require('mysql2/promise');
const cors = require('cors');
const neo4j = require('neo4j-driver');
const app = express();
const PORT = 3001;

app.use(cors());

// 创建 MySQL 连接池
const pool = mysql.createPool({
    host: 'localhost',
    user: 'root',
    password: '123456789',
    database: 'study',
    waitForConnections: true,
    connectionLimit: 10,
    queueLimit: 0
});
const driver = neo4j.driver(
    'bolt://localhost:7687',
    neo4j.auth.basic('neo4j', '123456789')
);

// 定义接口：获取 APT 信息
app.get('/api/apt-groups', async (req, res) => {
    try {
        const [rows] = await pool.query('SELECT id, organization AS name, introduction AS description FROM apt_info WHERE organization IS NOT NULL AND introduction IS NOT NULL');
        res.json(rows);
    } catch (err) {
        console.error('数据库查询失败:', err);
        res.status(500).json({ error: '数据库错误' });
    }
});

// 定义接口：根据 ID 获取组织详细信息
app.get('/api/apt-groups/:id', async (req, res) => {
    const id = req.params.id;
    try {
        // 获取组织基本信息
        const [aptInfoRows] = await pool.query('SELECT id, organization, introduction FROM apt_info WHERE id =?', [id]);
        if (aptInfoRows.length === 0) {
            return res.status(404).json({ error: '未找到该组织信息' });
        }
        const aptInfo = aptInfoRows[0];

        // 获取组织使用的域名
        const [domainRows] = await pool.query('SELECT domain FROM domain WHERE apt_organization =?', [aptInfo.organization]);
        const domains = domainRows.map(row => row.domain);

        // 获取组织使用的 IP
        const [ipRows] = await pool.query('SELECT ip FROM bad_ip WHERE apt_organization =?', [aptInfo.organization]);
        const ips = ipRows.map(row => row.ip);
const [vpsRows] = await pool.query('SELECT vps_provider_preference FROM apt_vps_preference WHERE apt_group =?', [aptInfo.organization]);
        const vpsProviders = vpsRows.map(row => row.vps_provider_preference);
        const result = {
            ...aptInfo,
            domains,
            ips,
            vpsProviders
        };

        res.json(result);
    } catch (err) {
        console.error('数据库查询失败:', err);
        res.status(500).json({ error: '数据库错误' });
    }
});

// 工具函数：Neo4j DateTime 转字符串
function formatNeo4jDateTime(dateTimeObj) {
    if (!dateTimeObj || typeof dateTimeObj !== 'object') return null;
    const { year, month, day, hour, minute, second } = dateTimeObj;
    return `${year.low}-${String(month.low).padStart(2, '0')}-${String(day.low).padStart(2, '0')} ` +
           `${String(hour.low).padStart(2, '0')}:${String(minute.low).padStart(2, '0')}:${String(second.low).padStart(2, '0')}`;
}

app.get('/api/apt-groups/:organization/neo4j-graph', async (req, res) => {
    const organization = req.params.organization;
    const session = driver.session();
    try {
        const result = await session.run(
            'MATCH (org:Organization {name: $organization})-[r]-(n) RETURN org, r, n, n.created_at AS created_at',
            { organization }
        );
        const nodes = [];
        const links = [];

        result.records.forEach(record => {
            const org = record.get('org');
            const rel = record.get('r');
            const node = record.get('n');
            const created_at = record.get('created_at');

            // 添加组织节点
            if (!nodes.some(n => n.id === org.identity.toString())) {
                nodes.push({
                    id: org.identity.toString(),
                    label: org.properties.name,
                    type: 'organization'
                });
            }

            // 确定节点类型
            const nodeType = node.labels[0];

            // 更稳健的 label 提取方式
            let label = '未知';
            if (nodeType === 'Domain' && node.properties.domain) {
                label = node.properties.domain;
            } else if (nodeType === 'IP' && node.properties.ip) {
                label = node.properties.ip;
            } else if (node.properties.name) {
                label = node.properties.name;
            } else if (node.properties.value) {
                label = node.properties.value;
            }

            // 添加与组织连接的节点（如 Domain、IP）
            if (!nodes.some(n => n.id === node.identity.toString())) {
                nodes.push({
                    id: node.identity.toString(),
                    label,
                    type: nodeType,
                    created_at:  formatNeo4jDateTime(created_at)
                });
            }

            // 添加关系（边）
            links.push({
                from: org.identity.toString(),
                to: node.identity.toString(),
                type: rel.type
            });
        });

        res.json({ nodes, links });
    } catch (err) {
        console.error('Neo4j 查询失败:', err);
        res.status(500).json({ error: 'Neo4j 数据库错误' });
    } finally {
        session.close();
    }
});

// 定义接口：获取 VPS 信息
app.get('/api/vps-providers', async (req, res) => {
    try {
        const [rows] = await pool.query('SELECT cryptocurrency_supproted_VPSProvider, Features, `Reasons to Choose`, Homepage FROM vps_providers_info');
        res.json(rows);
    } catch (err) {
        console.error('数据库查询失败:', err);
        res.status(500).json({ error: '数据库错误' });
    }
});

// 定义接口：获取 used_ip_details 表的数据
// 定义接口：获取 used_ip_details 表的数据
app.get('/api/used-ip-details', async (req, res) => {
    try {
        const [ipRows] = await pool.query(
            `SELECT apt_group, used_ip, asn, asn_country_code AS asnCountryCode, asn_description AS asnDescription 
             FROM used_ip_details`
        );

        res.json({
            data: ipRows
        });
    } catch (err) {
        console.error('数据库查询失败:', err);
        res.status(500).json({ error: '数据库错误' });
    }
});
// 定义接口：根据组织名称获取偏好的 VPS 服务商信息
app.get('/api/apt-groups/:organization/apt-vps-preference', async (req, res) => {
    const organization = req.params.organization;
    try {
        const [rows] = await pool.query('SELECT vps_provider_preference FROM apt_vps_preference WHERE apt_group =?', [organization]);
        const vpsProviders = rows.map(row => row.vps_provider);
        res.json(vpsProviders);
    } catch (err) {
        console.error('数据库查询失败:', err);
        res.status(500).json({ error: '数据库错误' });
    }
});
app.listen(PORT, () => {
    console.log(`服务器已启动，访问 http://localhost:${PORT}`);
});